﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Worktool.CODE_Chris.SQL_DATA;

namespace Worktool.CODE_Chris.DataSourceHelpers
{
    public class DmxDevicesHelper
    {
        private DmxDevices parent;
        private GeneralHelper helper;
        private SQL sql;

        private DataGridView gridView1;
        private SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();
        private BindingSource bindingSource1 = new BindingSource();
        private DataTable dataTable1;

        private String select1;
        private String update1;

        private Boolean isPaste = false;
        private int countPasteError = 0;

        public DmxDevicesHelper(DataGridView gridView1, DmxDevices parent)
        {
            this.parent = parent;
            this.gridView1 = gridView1;
            init();
        }

        private void init()
        {
            sql = new SQL();
            helper = new GeneralHelper();

            populateSQLStrings();
        }

        #region Methoden für gridView1 (Hauptmaske)
        public void getData1()
        {
            try
            {
                gridView1.DataSource = bindingSource1;

                sqlDataAdapter1 = new SqlDataAdapter(select1, Login.connection);
                sqlDataAdapter1.UpdateCommand = new SqlCommand(update1, new SqlConnection(Login.connection));

                //Neu zu schreibende Parameter
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@universe", SqlDbType.NVarChar, 50, "universe");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@dmxAdress", SqlDbType.Int, 10, "dmxAdress");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateUser", SqlDbType.NVarChar, 50, "updateUser");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@updateDate", SqlDbType.Date, 10, "updateDate");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@fixture", SqlDbType.Int, 50, "fixture");

                //Daten aus der Originalquelle
                SqlParameter parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Int, 50, "ID");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_universe", SqlDbType.NVarChar, 50, "universe");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_dmxAdress", SqlDbType.Int, 10, "dmxAdress");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateUser", SqlDbType.NVarChar, 50, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_updateDate", SqlDbType.Date, 10, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_fixture", SqlDbType.Int, 10, "fixture");
                parameter.SourceVersion = DataRowVersion.Original;

                //Check auf Null-Werte
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_universe", SqlDbType.Int, 50, "universe");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_dmxAdress", SqlDbType.Int, 10, "dmxAdress");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateUser", SqlDbType.Int, 50, "updateUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_updateDate", SqlDbType.Int, 10, "updateDate");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_fixture", SqlDbType.Int, 50, "fixture");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter1);

                // Populate a new data table and bind it to the BindingSource.
                dataTable1 = new DataTable();
                dataTable1.Locale = System.Globalization.CultureInfo.InvariantCulture;
                sqlDataAdapter1.Fill(dataTable1);
                bindingSource1.DataSource = dataTable1;

                gridView1.AutoGenerateColumns = false;

                //Zellennachbearbeitung
                setHiddenColumns1();
                setDisabledColumns1();

                //Eventhandler setzen
                gridView1.CellFormatting -= new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit -= new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellValueChanged -= new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);

                gridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit += new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellValueChanged += new DataGridViewCellEventHandler(this.gridView1_CellValueChanged);
            }
            catch (Exception e)
            {
                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
        }

        private void gridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            gridView1.CurrentRow.Cells["updateDate"].Value = DateTime.Today;
            gridView1.CurrentRow.Cells["updateUser"].Value = Login.user;
        }

        private void gridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //Hier werden die Daten der editierten Zelle wieder in die Datenbank geschrieben
            gridView1.BindingContext[gridView1.DataSource].EndCurrentEdit();
            updateGridView1();
        }

        public Boolean updateGridView1()
        {
            try
            {
                sqlDataAdapter1.Update(dataTable1);
                return true;
            }
            catch (DBConcurrencyException e)
            {
                if (isPaste)
                {
                    countPasteError++;
                }
                else
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Die Datensätze werden neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }

                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                return false;
            }
        }

        private void gridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex != -1)
            {
                DataGridViewColumn col = gridView1.Columns[e.ColumnIndex];

                //Blaue Zellen
                if (col.Name == "Manufacturer" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Model" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Venue" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Positions_ID" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "JLM-Number" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Discription" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Technische_Ausprägung_1" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Technische_Ausprägung_2" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Technische_Ausprägung_3" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Seriennummer" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateUser" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "updateDate" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
            }
        }

        private void setDisabledColumns1()
        {
            try
            {
                gridView1.Columns["Manufacturer"].ReadOnly = true;
                gridView1.Columns["Model"].ReadOnly = true;
                gridView1.Columns["Venue"].ReadOnly = true;
                gridView1.Columns["Positions_ID"].ReadOnly = true;
                gridView1.Columns["JLM-Number"].ReadOnly = true;
                gridView1.Columns["Discription"].ReadOnly = true;
                gridView1.Columns["Technische_Ausprägung_1"].ReadOnly = true;
                gridView1.Columns["Technische_Ausprägung_2"].ReadOnly = true;
                gridView1.Columns["Technische_Ausprägung_3"].ReadOnly = true;
                gridView1.Columns["Seriennummer"].ReadOnly = true;
                gridView1.Columns["updateUser"].ReadOnly = true;
                gridView1.Columns["updateDate"].ReadOnly = true;
            }
            catch (Exception e)
            {

            }
        }

        private void setHiddenColumns1()
        {
            try
            {
                gridView1.Columns["fk_materialDetailID"].Visible = false;
                gridView1.Columns["ID"].Visible = false;
            }
            catch (Exception e)
            {

            }
        }

        public DataTable getTable1()
        {
            return dataTable1;
        }
        #endregion

        #region SQL-Befehle als Strings
        private void populateSQLStrings()
        {
            select1 = "SELECT dmxAdress, universe, fixture, Venue, Model, Manufacturer, Discription, Positions_ID, Technische_Ausprägung_1, Technische_Ausprägung_2, Technische_Ausprägung_3, [JLM-Number], Seriennummer, updateDate, updateUser, fk_materialDetailID, ID FROM vw_eng_dmxDevices";
            update1 = "UPDATE tbl_dmxDevices SET universe = @universe, dmxAdress = @dmxAdress, updateUser = @updateUser, updateDate = @updateDate, fixture = @fixture "
                        +"WHERE (ID = @Original_ID) "
                        +"AND (@IsNull_universe = 1 AND universe IS NULL OR universe = @Original_universe) "
                        +"AND (@IsNull_dmxAdress = 1 AND dmxAdress IS NULL OR dmxAdress = @Original_dmxAdress) "
                        +"AND (@IsNull_updateUser = 1 AND updateUser IS NULL OR updateUser = @Original_updateUser) "
                        +"AND (@IsNull_updateDate = 1 AND updateDate IS NULL OR updateDate = @Original_updateDate) "
                        +"AND (@IsNull_fixture = 1 AND fixture IS NULL OR fixture = @Original_fixture) ";
        }
        #endregion
    }
}
